import traceback
from flask import Blueprint,render_template,jsonify,request,abort,send_from_directory, redirect, url_for
from requests.api import get
from sqlalchemy import func, and_
from sqlalchemy.log import echo_property
from sqlalchemy.orm import mapper
from sqlalchemy.sql.expression import table
from sqlalchemy.sql.operators import exists
import  models
from urllib.parse import quote
import json
from werkzeug.serving import WSGIRequestHandler
WSGIRequestHandler.protocol_version = "HTTP/1.1"
from werkzeug.utils import secure_filename
import os
import config
from config import debug, log as log
from config import makeNewSession as makeNewSession
from config import safe_file_name,fieldName
import re
import time
from datetime import datetime,timedelta
from uuid import uuid4
import copy
import zipfile
import openpyxl

formDatabase = Blueprint('FormDatabase',__name__)

############################# 表单提交数据

# 查询表单数据库
@formDatabase.route("/formTableDatabase")
def formTableDatabase():
    curPage = request.args.get("page",1,type=int)
    per_page = request.args.get("limit",15,type=int)
    getData = json.loads(request.args.get("searchParams","{}"))
    # print(getData, bool(getData),"text",getData)
    session = makeNewSession()
    if getData:
        stime = getData.get("datetime",None)
        if not stime: stime = f"2022-01-03 - {(datetime.now()+timedelta(4)).date()}"
        stime  = stime.split(" - ") 
        print("stime",stime)
        condition = and_(
            models.FormDatabase.tableNickName.contains(getData.get("tableNickName", '')) if getData.get("tableNickName", '') else True,
            models.FormDatabase.formID.contains(getData.get("formID", '') ) if getData.get("formID", '') else True,
            models.FormDatabase.beizhu.contains(getData.get("beizhu", '') ) if getData.get("beizhu", '') else True,
            models.FormDatabase.datetime >= stime[0],
            models.FormDatabase.datetime <= stime[1]
        )

    # 分页
    all_ = session.query(models.FormDatabase).offset((curPage-1)*per_page).limit(per_page)   if not getData else session.query(models.FormDatabase).filter(condition).offset((curPage-1)*per_page).limit(per_page)  

    # count  = session.query(func.count(all_)).scalar()
    # 获取总条数
    count =session.query(func.count(models.FormDatabase.tableID)).scalar() if not getData else session.query(func.count(models.FormDatabase.tableID)).filter(condition).scalar()

    result = {
        "code": 0,
        "msg": "",
        "count": count,
        "data": []
        }
    debug("all_",all_)
    for each in all_:
        result['data'].append(each.to_json())
    session.close()
    return jsonify(result)



# 添加数据表
@formDatabase.route("/addFormTable", methods=["POST"])
def addGroupData():
    getData = request.get_json()
    if not getData: return "you need post some args!",406
    if not isinstance(getData["formID"], int): getData["formID"] = int(getData["formID"])
    session = makeNewSession()

    try:
        obj = models.FormDatabase(**getData)
        print("obj",obj.to_json())
        session.add(obj)
        session.commit()

        q = session.query(models.Form).filter(models.Form.formID==int(getData.get("formID"))).first()


        sqlFields = []
        for field in json.loads(q.dbs_fields.replace("'",'"')):
            if field[1] == "text":
                sqlFields.append(f"{fieldName(field[0])} TEXT")
            elif field[1] == "upload":
                sqlFields.append(f"{fieldName(field[0])} INTEGER")

        sql = '''
        CREATE TABLE formTable{tableID}(
            formID INTEGER NOT NULL,
            stuID INTEGER NOT NULL PRIMARY KEY,
            stuName VARCHAR(10) NOT NULL,
            classesID INTEGER NOT NULL,
            datetime DATETIME DEFAULT (datetime('now','localtime')),
            {otherFields},
            FOREIGN KEY(stuID) REFERENCES students(uid),
            FOREIGN KEY(formID) REFERENCES forms(formID)
            );
        '''.format(tableID=obj.tableID,otherFields=',\n'.join(sqlFields))
        # sql = "drop table formTable1"
        print("sql", sql)
        session.execute(sql)
        session.commit()
        session.close()
        return "add FormTable successfully"
    except:
        print(getData,"插入数据库失败")
        traceback.print_exc()
        session.query(models.FormDatabase).filter(models.FormDatabase.tableID==obj.tableID).delete()
        session.commit()
        session.close()
        return ("add FormTable failure",500)


# 删除数据表
@formDatabase.route("/delFormTable",methods=["POST"])
def delFormTable():
    getData = request.get_json()
    if not getData: return "you need post some args!",406
    session = makeNewSession()
    try:
        
        session.execute(f"drop table formTable{getData['tableID']}")
        session.query(models.FormDatabase).filter(models.FormDatabase.tableID==getData['tableID']).delete()
        session.commit()
        session.close()
        return "del successfully"
    except:
        session.rollback()
        session.close()
        return "del failure",500
# 删除数据表数据 多条
@formDatabase.route("/delFormTables",methods=["POST"])
def delFormTables():
    getDatas = request.get_json()
    # print(getDatas)
    session = makeNewSession()
    try:
        for table in getDatas:
            session.execute(f"drop table formTable{table}")
        session.query(models.FormDatabase).filter(models.FormDatabase.tableID.in_(getDatas)).delete()
        session.commit()
        session.close()
        return "del successfully"
    except:
        session.rollback()
        session.close()
        return "del failure",500



# 更新数据表信息
@formDatabase.route("/updateFormTable",methods=["POST"])
def updateFormData():
    getData = request.get_json()
    if not getData: return "you need post some args!",406
    print(getData)
    session = makeNewSession()
    try:
        session.query(models.FormDatabase).filter(models.FormDatabase.tableID==getData["tableID"]).update(getData)
        session.commit()
        session.close()
        return "update successfully"
    except:
        traceback.print_exc()
        session.rollback()
        session.close()
        return "update failure",500


# 获取提交数据表单的数据
@formDatabase.route("/getFormTable")
def getFormTable():
    get_tableID = request.args.get("tableID",type=int)
    limit= request.args.get("limit", 50,type=int)
    page = request.args.get("page",0, type=int)
    searchParams = request.args.get("searchParams")
    searchParams = json.loads(searchParams) if searchParams else None
    cls_ = request.args.get("class", 0,type=int)
    if not searchParams:
        searchParams = {"classesID":cls_}
    else:
        if "classesID" not in searchParams or not searchParams["classesID"]:
            searchParams["classesID"] = cls_
    print("searchParams",searchParams)
    session = makeNewSession()
    q_fields = session.execute(f"PRAGMA table_info(formTable{get_tableID})").fetchall()
    fields = []
    for field in q_fields:
        fields.append(field[1])
    print("fields", fields)
    postFields = ["stuID","stuName","classesID","datetime"]
    postFields.extend(fields[5:])
    print("postFields", postFields)
    # conditions = " and ".join([
    #     f"classesID = {cls_}" if cls_ else "true"
    # ])
    conditions = []
    if searchParams:
        for k in searchParams:
            if searchParams[k]:
                if k == "datetime":
                    st,ed = searchParams[k].split(" - ")
                    conditions.append(f"datetime >= '{st}' and datetime <= '{ed}' ")
                else:
                    conditions.append(f"{k}={searchParams[k]}")
    if not conditions:conditions.append("true")
    datas = session.execute(f"select {','.join(postFields)} from formTable{get_tableID}  where {' and '.join(conditions)} LIMIT :limit OFFSET :offset", {"limit":limit, "offset":page-1}).fetchall()
    count = session.execute(f"SELECT count(formTable{get_tableID}.stuID) AS count_1 FROM formTable{get_tableID}").fetchall()[0][0]
    
    
    dbs_fields = session.execute("select forms.dbs_fields from formDatabase, forms where formDatabase.tableID=:tableID and formDatabase.formID=forms.formID;",{"tableID":get_tableID}).fetchall()[0][0]
    dbs_fields = json.loads(dbs_fields)
    # print("dbs_fields", dbs_fields)
    
    readFields = [ "学号","姓名", "班级", "提交时间"]
    readFields.extend(map(lambda v: v[0], dbs_fields))
    # 字段类型 
    typeFields = ["text" for i in range(len(postFields))]
    tmp_dict = dict(tuple(zip(postFields,typeFields)))
    for field in dbs_fields:
        if field[1] == "upload":
            tmp_dict[fieldName(field[0])] = "upload"
    typeFields = []
    for e in postFields:
        typeFields.append(tmp_dict[e])
    del tmp_dict
    
    fields_len = []
    result = {
            "code": 0,
            "msg": "",
            "count": count,
            "data": [],
            "fields":None
            }
    if datas:
        for each in datas[0]:
            fields_len.append(90+len(str(each))*5)
    else:
        for each in postFields:
            fields_len.append(90+len(str(each))*5)

    for data in datas:
        result['data'].append(dict(zip(postFields,data)))
    result['fields'] = list(zip(postFields, readFields,fields_len,typeFields))
    print(result["fields"])
    session.close()
    return jsonify(result)
     




# 学生表单提交文件
@formDatabase.route("/formUploadFile", methods=["POST"])
def formUploadFile():
    print(request.form)
    print(request.get_data(as_text=True))
    print(request.files)
    if request.method == 'POST':
        tableID = request.form.get("tableID", type=int)
        fileFieldName = request.form.get("fileFieldName")
        stuID = request.form.get("stuID", type=int)
        id_ = request.form.get("btn_id")
        # getData = request.get_json()
        if not tableID or not fileFieldName or not stuID : return "you need post some args!",406
        session = makeNewSession()
        f = request.files['file']
        print("fileName",f.filename)
        ext = os.path.splitext(f.filename)[1]
        newFileName = f"{uuid4().hex}{ext}"
        print("newFileName",newFileName)
        upload_dir = os.path.join(config.FORM_UPLOAD_FILES_DIR, f"formTable{tableID}")
        if not os.path.exists(upload_dir): os.mkdir(upload_dir)
        upload_path = os.path.join(upload_dir,newFileName)  #注意：没有的文件夹一定要先创建，不然会提示没有该路径
        f.save(upload_path)
        filename = f.filename
        f.close()
        try:
            if not (tableID and stuID and fileFieldName): return "文件提交失败"
            fileID = f"{tableID}_{stuID}_{fileFieldName}"
            m = dict(
                    fileID=fileID,
                    formTableID=tableID,
                    formTableFileField=fileFieldName,
                    stuID=stuID,
                    userFileName=safe_file_name(filename),
                    serverFileName=newFileName,
                    fileSize=os.path.getsize(upload_path))

            q = session.query(models.FormTableUploadFile).filter(models.FormTableUploadFile.fileID==fileID)
            if not q.count():
                fi = models.FormTableUploadFile(**m)
                session.add(fi)
            else:
                del m["fileID"]
                q.update(m)
                fi = q.first()
            session.commit()
            return jsonify({"code":0,"data":[{
                "fp_relative":fi.userFileName,
                "fileID": fi.fileID,
                "btn_id":id_
                }]})
        except:
            traceback.print_exc()
            session.rollback()
        session.close()


    return jsonify({"code":1,"data":[]})


## 表单提交数据
@formDatabase.route("/formTablePost", methods=["POST"])
def formCreatedPost():

    getData = request.get_json()
    if 'file' in getData:
        hasFile = True
        del getData["file"]    
    else:
        hasFile = False
    if not getData: return "you need post some args!",406
    print(getData)
    session = makeNewSession()
    get_tableID = int(getData['tableID'])
    formID = int(getData['formID'])
    try:
        # 若文件已提交,设置文件字段为文件库中id,否则为None
        if hasFile:
            form = session.query(models.Form).filter(models.Form.formID==formID).first()
            for field in json.loads(form.dbs_fields):
                if field[1] == "upload":
                    fname = fieldName(field[0])
                    fid = f"{get_tableID}_{getData['stuID']}_{fname}"
                    f = session.query(models.FormTableUploadFile).filter(models.FormTableUploadFile.fileID==fid).first()
                    if f:
                        getData[fname] = fid
                    else:
                        getData[fname] = None
                        

        stu = session.query(models.Student).filter(models.Student.uid==int(getData['stuID'])).first()
        getData_ = copy.deepcopy(getData)
        del getData_["tableID"];del getData_["formID"];del getData_["stuID"]
        cols, vals = zip(*tuple(getData_.items()))
        print("getData", getData)
        q = session.execute(f"select stuID from formTable{get_tableID} where stuID=:ID;",{"ID":stu.uid}).fetchall()
        if not q:
            session.execute(f'''INSERT INTO formTable{get_tableID} (formID,stuID,stuName,classesID,{','.join(cols)})
                VALUES ({formID},{int(getData['stuID'])},'{stu.name}',{stu.banji},{','.join([f":{v}" for v in cols])});
                ''',dict(zip(cols,vals)))
        else:
            session.execute(f"update formTable{get_tableID} set {', '.join([f'{v}=:{v}' for v in cols])} where stuID={stu.uid}", dict(zip(cols,vals)))
        # formtable = models.FormTable(**getData)
        # session.add(formtable)
        # r = redirect(f"/data_link?tableID={get_tableID}&class={stu.banji}")
        r = render_template("post_success.html", stuName=stu.name)
        session.commit()
        session.close()
        return r
    except:
        traceback.print_exc()
        session.rollback()
        session.close()
        return 'err',500

@formDatabase.route("/formPostSuccess")
def formPostSuccess():
    stuID = request.args.get("stuID", type=int)
    tableID = request.args.get("tableID", type=int)
    if not stuID or not tableID: return render_template("404.html"),404
    session = makeNewSession()
    stu = session.query(models.Student).filter(models.Student.uid==stuID).first()
    count = session.execute(f"select count(stuID) from formTable{tableID}").fetchall()[0][0]
    print(count)
    r = render_template("post_success.html", stuName=stu.name,index=count)
    session.close()
    return r



## 删除表单提交数据
@formDatabase.route("/delFormTablePostData", methods=["POST"])
def delFormTablePostData():
    getData = request.get_json()
    if not getData: return "need some args",406

    session = makeNewSession()
    
    try:
        session.qeury(models.FormTableUploadFile).filter(models.FormTableUploadFile.formTableID==int(getData['tableID'])).delete()
        session.execute(f"delete from formTable{getData['tableID']} where stuID=:stuID", {"stuID":getData["stuID"]})
        session.commit()
        result = f"del {getData['stuID']}",200
    except:
        traceback.print_exc()
        session.rollback()
        result = "del fault",500
    session.close()
    return result


## 删除表单提交数据 多
@formDatabase.route("/delFormTablePostDatas", methods=["POST"])
def delFormTablePostDatas():
    getData = request.get_json()
    if not getData: return "need some args",406

    session = makeNewSession()
    print("dels getData", getData)
    try:
        stuIDs = ', '.join(map(lambda s: str(s),getData['stuIDs']))
        session.execute(f"delete from formTable{getData['tableID']} where stuID in ({stuIDs})")
        session.commit()
        result = f"del {stuIDs}",200
    except:
        traceback.print_exc()
        session.rollback()
        result = "del fault",500
    session.close()
    return result

# 下载表单提交文件
@formDatabase.route("/downloadFormPostFile", methods=["POST"])
def downloadFormPostFile():
    getData = request.get_json()
    if not getData: return "need some args",406
    data = getData["data"]
    tableID = getData["tableID"]
    classesID = getData.get("classesID",None)
    session = makeNewSession()
    flist = []
    zip_file_name = None
    if getData["type"] == "selected":
        qs = session.query(models.FormTableUploadFile).filter(models.FormTableUploadFile.fileID.in_(data)).all()
        for f_each in qs:
            fpath = os.path.join(config.FORM_UPLOAD_FILES_DIR, f"formTable{tableID}",f_each.serverFileName)
            fname = os.path.join(str(f_each.to_stu.banji), f"{f_each.to_stu.uid}_{f_each.to_stu.name}", f_each.userFileName)
            flist.append({"fpath":fpath,"fname":fname})

        zip_file_name = f"{{formTitle}}_文件_{len(qs)}人.zip"
    elif getData["type"] == "all":
        if classesID:
            qs = session.query(models.FormTableUploadFile).filter(models.FormTableUploadFile.formTableID==tableID,models.FormTableUploadFile.stuID.in_(session.query(models.Student.uid).filter(models.Student.banji==classesID))).all()
            zip_file_name = f"{{formTitle}}_文件_class_{classesID}.zip"
        else:
            qs = session.query(models.FormTableUploadFile).filter(models.FormTableUploadFile.formTableID==tableID).all()
            zip_file_name = f"{{formTitle}}_文件_全部.zip"
        for f_each in qs:
            fpath = os.path.join(config.FORM_UPLOAD_FILES_DIR, f"formTable{tableID}",f_each.serverFileName)
            fname = os.path.join(str(f_each.to_stu.banji), f"{f_each.to_stu.uid}_{f_each.to_stu.name}", f_each.userFileName)
            flist.append({"fpath":fpath,"fname":fname})
    else:
        return "deep arg is needed",500

    q = session.query(models.FormDatabase).filter(models.FormDatabase.tableID==tableID).first()
    formTitle = q.to_form.formTitle
    # zip_file_name = os.path.join(config.ZIP_DIR, zip_file_name.format(formTitle=formTitle))
    zip_file_name = zip_file_name.format(formTitle=formTitle)
    with zipfile.ZipFile(config.ZIP_FILE, "w", zipfile.ZIP_DEFLATED) as z:
        for f_obj in flist:
            z.write(f_obj['fpath'], f_obj['fname'], zipfile.ZIP_DEFLATED)
        z.close()
    
    return jsonify({"filename":quote(zip_file_name)})

            

# 获取zip文件, 与上面连用 用于a标签get获取文件
@formDatabase.route("/downloadZipFile", methods=["GET"])
def downloadZipFile():
    fname = request.args.get("filename")
    sdir, sname = os.path.split(config.ZIP_FILE)
    s = send_from_directory(sdir,sname,as_attachment=True)
    s.headers["Content-Disposition"] = f"attachment; filename={quote(fname)}"

    return s





@formDatabase.route("/exportTableData", methods=["POST"])
def exportTableData():
    getData = request.get_json()
    if not getData: return "need some args",406
    data = getData["data"]
    tableID = getData["tableID"]
    cols = getData["cols"]
    type_ = getData["type"]
    classesID = getData.get("classesID",None)
    session = makeNewSession()


    titles = []
    contents = {}
    try:
        # 匹配模式
        if type_ == "selected":
            tablename = f"select {len(data)}"
            contents[tablename]=[]
            for t in cols:
                titles.append(t[1])
            for d in data:
                content = []
                for t in cols:
                    content.append(d[t[0]])
                contents[tablename].append(content)
            filename = f"{{formName}}_{{tableName}}_数据表_{len(data)}人.xlsx"
        elif type_ == "all":
            for t in cols:
                titles.append(t[1])
            if classesID:
                clss = [(classesID,)]
                filename = f"{{formName}}_{{tableName}}_数据表_class_{classesID}.xlsx"
            else:
                clss = session.query(models.Classes.classesID).all()
                filename = f"{{formName}}_{{tableName}}_数据表_all.xlsx"
            for cls in clss:
                cls = cls[0]
                qs = session.execute(f"select {','.join(map(lambda d:d[0], cols))} from formTable{tableID} where classesID=:classesID",{"classesID":cls}).fetchall()
                if not len(qs):continue
                print("qs", tuple(qs))
                tablename = str(cls)
                contents[tablename] = tuple(qs)
        book = openpyxl.Workbook() 
        print("contents", contents)
        for table in contents:
            sheet = book.create_sheet(table,0)
            sheet.append(titles)
            for row in contents[table]:
                print(row, type(row))
                sheet.append(tuple(row))
        
        book.save(config.EXCEL_PATH)
        q = session.query(models.FormDatabase).filter(models.FormDatabase.tableID==tableID).first()
        result = jsonify({"filename":quote(filename.format(formName=q.to_form.formTitle,tableName=q.tableNickName))})
        session.close()
    except:
        traceback.print_exc()
        result = "error",500
    return result

# 获取excel文件, 与上面连用 用于a标签get获取文件
@formDatabase.route("/downloadExcel", methods=["GET"])
def downloadExcel():
    fname = request.args.get("filename")
    sdir, sname = os.path.split(config.EXCEL_PATH)
    s = send_from_directory(sdir,sname,as_attachment=True)
    s.headers["Content-Disposition"] = f"attachment; filename={quote(fname)}"

    return s

# 获取分享表单
@formDatabase.route("/data_link", methods=["GET"])
def data_link():
    tableID = request.args.get("tableID", type=int)
    classesID = request.args.get("class", type=int)

    return render_template("share_data_link.html",tableID=tableID, classesID=classesID)








# 中文转拼音
@formDatabase.route("/getFieldName", methods=["GET"])
def getFieldName():
    name = request.args.get("name")
    if name:
        return fieldName(name)
    else:
        return ''


# 获取学生ID
@formDatabase.route("/getStuID", methods=["POST"])
def getStuID():
    try:
        session = makeNewSession()
        q = session.query(models.Student.uid).all()
        print("q", q)
        q = tuple(map(lambda e: e[0],q))
        return jsonify(q)
    except:
        traceback.print_exc()
    session.close()
    return jsonify([]),500

# 获取banji
@formDatabase.route("/getClassesID", methods=["POST"])
def getClassesID():
    try:
        session = makeNewSession()
        q = session.query(models.Classes.classesID).all()
        print("q", q)
        q = tuple(map(lambda e: e[0],q))
        return jsonify(q)
    except:
        traceback.print_exc()
    session.close()
    return jsonify([]),500


